﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;

namespace DatabaseAdminTools.Connections
{
    public class Connection
    {
        /// <summary>
        /// Lop Connection bao gom cac thong so can thiet de ket noi toi csdl
        /// va thuc thi cac cau lenh sql truy xuat, thao tac voi csdl.
        /// </summary>
        #region Variables and Properties Definition
        private MySqlConnection Conn = null; // Doi tuong ket noi toi csdl.
        public String dbName { get; private set; } // Database name
        public String hostName { get; private set; } // Host name
        public String userName{ get; private set;} // User Name
        public String pass{ get; private set;} // Password
        public int port{ get; private set; } // Port

        /// <summary>
        /// Ten ket noi
        /// </summary>
        public String Name { set; get; } // Ten ket noi Connecion

        public string ConnectionStringWithoutPassword
        {
            get
            {
                return "server="+hostName+";"+"uid="+userName+";port="+port+";Charset='utf8'";
            }
        }

        /// <summary>
        /// Get, set chuoi ket noi. Lay thong tin tu chuoi ket noi de thiet lap cac gia tri khac cho Connecion (host, uid...)
        /// </summary>
        public string ConnectionString
        {
            set
            {
                Conn.ConnectionString = value;
                string[] val = value.Split(';');
                hostName = val[0].Split('=')[1].Trim();
                userName = val[1].Split('=')[1].Trim();
                pass = val[2].Split('=')[1].Trim();
                port = Int32.Parse(val[3].Split('=')[1].Trim());
            }
            get
            {
                return "server=" + hostName + ";" + "uid=" + userName + ";pwd=" + pass + ";port=" + port + ";Charset='utf8';";
            }
        }
        #endregion

        #region Constructors
        /// <summary>
        /// Create new empty Connection
        /// </summary>
        public Connection()
        {
            Conn = new MySqlConnection();
            dbName = hostName = userName = pass = "";
            port = 0;
        }

        /// <summary>
        /// Create new Connection with properties
        /// </summary>
        /// <param name="name">Connection name</param>
        /// <param name="host">Host name</param>
        /// <param name="port">Port</param>
        /// <param name="user">User name</param>
        /// <param name="passwrd">Password</param>
        public Connection(String name, String host, int port, String user, String passwrd)
        {
            setConnection(name, host, user, passwrd, port);
        }
        #endregion

        #region Connection process
        /// <summary>
        /// Thiet lap cac thong so can thiet de ket noi toi csdl.
        /// </summary>
        /// <param name="name"> Ten Connection (ten host)</param>
        /// <param name="host"> Host name / IP address. VD: 127.0.0.1 , localhost, ...</param>
        /// <param name="user"> User name. Ket noi toi csdl thong qua user</param>
        /// <param name="passwrd">Password</param>
        /// <param name="prt"> Ket noi thong qua port nao (mac dinh 3306)</param>
        /// <exception cref="Exception: Message =  Error Number + Error Message."></exception>
        public void setConnection(String name, String host, String user, String passwrd, int prt = 3306)
        {
            Name = name;
            hostName = host;
            port = prt;
            userName = user;
            pass = passwrd;
            Conn = new MySqlConnection();
            Conn.ConnectionString = "server="+host+";uid="+user+";pwd="+pass+";Charset='utf8';";
            try
            {
                Conn.Open();
            }
            catch (MySqlException ex)
            {
                Conn.Close();
                throw new Exception("[ERROR] " + ex.Number + " - " + ex.Message);
            }
            Conn.Close();
        }

        /// <summary>
        /// Set database of Connection, and try to Connect to it.
        /// </summary>
        /// <param name="database">Database name</param>
        /// /// <exception cref="Exception: Error Number va Error Message."></exception>
        public void setDatabase(String database)
        {
            if (Conn == null)
            {
                throw new Exception("Connection must be initialize !");
            }
            else
            {
                dbName = database;
                Conn.ConnectionString = "server=" + hostName + ";uid=" + userName + ";pwd=" + pass + ";database =" + dbName + ";Charset=utf8;";
            }
        }

        /// <summary>
        /// Dong ket noi
        /// </summary>
        public void closeConnection()
        {
            Conn.Close();
        }
        #endregion

        #region data process
        /// <summary>
        /// Lay cac thong tin dang bieu do (schema) ve ket noi, csdl, bang du lieu...
        /// </summary>
        /// <param name="str">Chuoi tuong ung de lay thong tin. VD: "Databases", "Tables", "Users", "MetaDataCollections".... </param>
        /// <returns>Data table chua thong tin can lay</returns>
        /// /// <exception cref="Exception: Message =  Error Number + Error Message."></exception>
        public System.Data.DataTable getSchemaInfo(String str)
        {
            try
            {
                Conn.Open();
                System.Data.DataTable data = Conn.GetSchema(str);
                Conn.Close();
                return data;
            }
            catch (MySqlException ex)
            {
                Conn.Close();
                throw new Exception("[ERROR] " + ex.Number + " - " + ex.Message);
            }
        }

        /// <summary>
        /// Lay thong tin tu csdl bang cach thuc thi cau lenh sql (SELECT, SHOW).
        /// </summary>
        /// <param name="sql">Cau lenh SQl (sql command).</param>
        /// <returns>Data table chua du lieu tra ve, null neu du lieu trong.</returns>
        /// <exception cref="Exception: Message =  Error Number + Error Message."></exception>
        public System.Data.DataTable getTable(String sql)
        {
            System.Data.DataTable data = new DataTable();
            try
            {
                Conn.Open();
                MySqlCommand command = new MySqlCommand(sql, Conn);
                MySqlDataAdapter adapter = new MySqlDataAdapter(command);
                adapter.Fill(data);
                Conn.Close();
            }
            catch (MySqlException e)
            {
                Conn.Close();
                throw new Exception("[ERROR] " + e.Number + " - " + e.Message);
            }
            return data;
        }

        /// <summary>
        /// Thuc thi mot cau lenh sql. Tra ve so dong bi anh huong boi cau lenh sql nay.
        /// </summary>
        /// <param name="sql">Cau lenh sql (sql command)</param>
        /// <exception cref="Exception: Message =  Error Number + Error Message."></exception>
        /// <returns>So row bi anh huong boi sql query (affected row)</returns>
        /// 
        public int executeQuery(string sql)
        {
            try
            {
                Conn.Open();
                MySqlCommand command = new MySqlCommand(sql, Conn);
                int i = command.ExecuteNonQuery();
                Conn.Close();
                return i;
            }
            catch (MySqlException e)
            {
                Conn.Close();
                throw new Exception("[ERROR] " + e.Number + " - " + e.Message);
            }
        }
        #endregion

        #region Database
        /// <summary>
        /// Create new database with character set and collation.
        /// 1. Both charset and collation are "default", the server charset and server collation are used.
        /// 2. If both charset X and collation Y are specified, charset X and collation Y are used.
        /// 3. If charset X is specified without collation (collation = "default"). Charset X and its default collation are used.
        /// 4. If collation Y is specified without Char set, the charset associates with Y and collation Y are used.
        /// </summary>
        /// <param name="name">Ten database</param>
        /// <param name="charset">character set</param>
        /// <param name="collation">database collation</param>
        /// <exception cref="Exception: Message = Error Number + Error message"
        public void createDatabase(string name, string charset, string collation)
        {
            string sql = "CREATE DATABASE IF NOT EXISTS " + name;
            if (!charset.ToLower().Equals("default"))
            {
                sql += " CHARACTER SET " + charset;
            }
            if (!collation.ToLower().Equals("default"))
            {
                sql += " COLLATE " + collation;
            }
            sql += ";";
            executeQuery(sql);
        }

        public void closeDatabase()
        {
            dbName = null;
            Conn.ConnectionString = "server=" + hostName + ";uid=" + userName + ";pwd=" + pass + ";Charset=utf8;";
        }

        public void dropDatabase(string database)
        {
            executeQuery("DROP DATABASE IF EXISTS " + database + ";");
        }

        public string[] getDatabaseInfo(string database)
        {
            string sql = "SELECT Default_Character_Set_Name, Default_Collation_Name from Information_Schema.SCHEMATA where Schema_Name = '" + database + "'";
            DataTable data = getTable(sql);
            return new string[] { data.Rows[0][0].ToString(), data.Rows[0][1].ToString() };
        }

        public void updateDatabaseInfo(string database, string charset, string collation)
        {
            string sql = "ALTER DATABASE " + database;
            sql += " CHARACTER SET " + (charset.ToLower().Equals("default")?"utf8":charset);
            if (!collation.ToLower().Equals("default"))
            {
                sql += " COLLATE " + collation;
            }
            sql += ";";
            executeQuery(sql);
        }

        /// <summary>
        /// Lay chuoi cac gia tri charater set
        /// </summary>
        /// <returns></returns>
        public string[] getCharacterSet()
        {
            string[] result;
            try
            {
                DataTable table = getTable("SHOW CHARACTER SET");
                int rows = table.Rows.Count;
                result = new string[rows];
                for (int i = 0; i < rows; i++)
                {
                    result[i] = table.Rows[i][0].ToString();
                }
                return result;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// Lay chuoi cac conllation
        /// </summary>
        /// <returns></returns>
        public string[] getCollation()
        {
            string[] result;
            try
            {
                DataTable table = getTable("SHOW COLLATION");
                int rows = table.Rows.Count;
                result = new string[rows];
                for (int i = 0; i < rows; i++)
                {
                    result[i] = table.Rows[i][0].ToString();
                }
                return result;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }
        #endregion
    }

    public class User
    {
        public string Name { get; private set; }
        public string Host { get; private set; }
        public bool[] Privileges { get; set; }
        private string[] privilegesColumns;
        private Connection conn;

        public User(Connection _conn, String name, String host)
        {
            conn = _conn;
            Name = name;
            Host = host;
        }

        public User(Connection _conn)
        {
            conn = _conn;
            Name = Host = "";
        }

        public void setData()
        {
            try
            {
                string sql = "SELECT * FROM mysql.user where Host = '" + Host + "' and user = '" + Name + "'";
                DataTable privData = conn.getTable(sql);
                if (privData != null)
                {
                    DataRow userData = privData.Rows[0];
                    Privileges = new bool[28];
                    for (int i = 0; i < 28; i++)
                    {
                        Privileges[i] = (userData[i + 3].Equals("Y"));
                    }
                    privilegesColumns = new String[28];
                    for (int i = 0; i < 28; i++)
                    {
                        privilegesColumns[i] = privData.Columns[i + 3].ColumnName;
                    }
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// Create new User.
        /// </summary>
        /// <param name="password">password. If password is empty (""), the user password dose not set.</param>
        /// <exception cref="Exception">Throw an exception if CREATE command error.</exception>
        public void create(string password)
        {
            string sql = "CREATE USER '"+Name+"'@'"+Host+"'";
            if (!password.Equals(""))
            {
                sql += " IDENTIFIED BY '" + password + "'";
            }
            sql += ";";
            conn.executeQuery(sql);
            sql = "GRANT ALL PRIVILEGES ON *.* TO '" + Name + "'@'" + Host + "' " + " WITH GRANT OPTION;";
            conn.executeQuery(sql);
        }

        /// <summary>
        /// Drop User.
        /// </summary>
        /// <exception cref="Exception">Throw an exception if drop command error.</exception>
        public void drop()
        {
            conn.executeQuery("DROP USER '" + Name + "'@'" + Host + "'");
        }

        public void updatePrivileges()
        {
            string sql = "UPDATE mysql.user SET ";
            int i;
            for (i = 0; i < 27; i++)
            {
                sql += privilegesColumns[i] + " = '" + (Privileges[i] ? "Y" : "N") + "',";
            }
            sql += privilegesColumns[i] + " = '" + (Privileges[i] ? "Y" : "N") + "' WHERE Host = '" + Host + "' and User = '" + Name + "';";
            conn.executeQuery(sql);
            sql = "FLUSH PRIVILEGES;";
            conn.executeQuery(sql);
        }

        public void update(string _name, string _host, string password)
        {
            string sql = "UPDATE mysql.user SET Host = '" + _host + "', User = '" + _name + "'";
            if (!password.Equals("")) sql += ", Password =  PASSWORD('" + password + "')";
            sql += " WHERE Host = '" + Host + "' and User = '" + Name + "';";
            conn.executeQuery(sql);
            sql = "FLUSH PRIVILEGES;";
            conn.executeQuery(sql);
        }

        public override string ToString()
        {
            return Name + "@" + Host;
        }
    }
}